﻿using System;
using System.Data.SqlClient;
using System.Data;
using HHL_Util;

namespace HHL_Acc.HHL_DaoAcc.DataObject
{
    public class CBaseDao
    {
        // protected string ConnectionString = "Data Source=100.100.100.100;Initial Catalog=i_SYS_COREAPP;MultipleActiveResultSets=True;Max Pool Size=500;User ID=iMarkets_001_01;Password=123456";
        protected string ConnectionString;

        public CBaseDao()
        {
            ConnectionString =
                System.Configuration.ConfigurationManager.ConnectionStrings["HHL_Connection"].ConnectionString;
        }

        //protected string ConnectionString = "Data Source=192.168.40.214;Initial Catalog=i_SYS_COREAPP;MultipleActiveResultSets=True;Max Pool Size=500;User ID=thien.l.do;Password=123456";
        protected SqlConnection Connection = null;

        protected void OpenConnection()
        {
            try
            {
                if (Connection == null)
                {
                    Connection = new SqlConnection(ConnectionString);
                }

                if (Connection.State != System.Data.ConnectionState.Open)
                {
                    Connection.Open();
                }
            }
            catch
            {
                CommonUtil.WriteToLogFile("Can not access database");
            }
        }

        protected void CloseConnection()
        {
            if (Connection != null)
            {
                if (Connection.State != System.Data.ConnectionState.Closed)
                {
                    Connection.Close();
                }
            }
        }

        protected DataSet ExcuteSql(string pQuery)
        {
            return ExcuteSql(pQuery, CommandType.Text);
        }

        protected DataSet ExcuteSql(string pQuery, CommandType type)
        {
            return ExcuteSql(pQuery, CommandType.Text, null);
        }

        protected DataSet ExcuteSql(string pQuery, CommandType type, params SqlParameter[] pars)
        {
            try
            {
                OpenConnection();
                SqlCommand com = new SqlCommand(pQuery, Connection);
                com.CommandType = type;
                if (pars != null)
                {
                    com.Parameters.AddRange(pars);
                }
                SqlDataAdapter ada = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                ada.Fill(ds);

                return ds;
            }
            catch (SqlException ass)
            {
                CommonUtil.WriteToLogFile("Can not execute query: " + pQuery + "\n" + ass.Message);
            }
            finally
            {
                CloseConnection();
            }
            return null;
        }

        protected int ExcuteNoneQuery(string pQuery, params SqlParameter[] paras)
        {
            try
            {
                OpenConnection();
                SqlCommand com = new SqlCommand(pQuery, Connection);
                com.Parameters.AddRange(paras);
                int iResult = com.ExecuteNonQuery();

                return iResult;
            }
            catch (SqlException ass)
            {
                CommonUtil.WriteToLogFile("Can not execute query: " + pQuery + "\n" + ass.Message);
            }
            finally
            {
                CloseConnection();
            }
            return int.MinValue;
        }

        protected int DeleteNoneQuery(string pQuery)
        {
            try
            {
                OpenConnection();
                SqlCommand com = new SqlCommand(pQuery, Connection);
                int iResult = com.ExecuteNonQuery();

                return iResult;
            }
            catch (SqlException ass)
            {
                CommonUtil.WriteToLogFile("Can not execute query: " + pQuery + "\n" + ass.Message);
            }
            finally
            {
                CloseConnection();
            }
            return int.MinValue;
        }
    }
}